﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using FL.Aspose;
using FL.DBModule;
using FL.Bussiness;

namespace FL.DataImport
{
    public class DataImportBase
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public System.Data.DataSet GetExeclSet(string FileName)
        {
            ImportCell importCell = new ImportCell(FileName);
            return importCell.GetSheetDataToDataSet();
        }

        /// <summary>
        /// 获得EXECL集合
        /// </summary>
        /// <param name="FileName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public System.Data.DataTable GetExeclTable(string FileName, string TableName)
        {
            return GetExeclSet(FileName).Tables[TableName];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public System.Data.DataTable GetExeclTable(string FileName)
        {
            return GetExeclSet(FileName).Tables[0];
        }

        /// <summary>
        /// 避免重复导入数据
        /// </summary>
        /// <param name="sourceDT"></param>
        /// <param name="TableName"></param>
        /// <param name="strWhere"></param>
        /// <param name="Fields"></param>
        public void Import(DataTable sourceDT, string TableName, string strWhere, string Fields)
        {
            BaseBussnies bll = new BaseBussnies();
            System.Data.DataTable dt = bll.GetDataBySqlComm("Select * from t_sys_import_setting where table_name='" + TableName + "'");
            //dt.Select().ToList();
            (new FL.DataImport.DataConvert()).UpdateDataTableColumnName(sourceDT, dt);
            foreach (DataRow row in sourceDT.Rows)
            {
                string[] _f = Fields.Split(',');
                object[] _obj = new object[_f.Length];
                for (int _index = 0; _index < _f.Length; _index++)
                {
                    _obj[_index] = row[_f[_index]].ToString();
                }
                string strSql = "select * from " + TableName + " where " + string.Format(strWhere, _obj);
                System.Data.DataTable _dt = bll.GetDataBySqlComm(strSql);
                if (_dt.Rows.Count > 0)
                {
                    continue;
                }

                DataTableBaseEntity entity = new DataTableBaseEntity();
                entity.TableName = TableName;
                string[] strKeyArrary = entity.GetKey();
                if (strKeyArrary != null)
                {
                    entity.KeyField = strKeyArrary[0];
                    entity.KeyFieldDataType = strKeyArrary[1];
                }
                entity.CreateDT();
                foreach (System.Data.DataColumn col in sourceDT.Columns)
                {
                    try
                    {
                        entity.SetValue(col.ColumnName, row.ItemArray[sourceDT.Columns[col.ColumnName].Ordinal]);
                        if (col.ColumnName == "employee_code")
                        {
                            DataRow dr = GetEmployee(row.ItemArray[sourceDT.Columns["employee_code"].Ordinal].ToString());
                            entity.SetValue("employee_id", dr.ItemArray[dr.Table.Columns["id"].Ordinal]);
                        }
                    }
                    catch
                    {

                    }

                    DataRow _row = dt.Select("field_name='" + col.ColumnName + "'").First();
                    string sql_command = _row.ItemArray[dt.Columns["sql_command"].Ordinal] == null ? "" : _row.ItemArray[dt.Columns["sql_command"].Ordinal].ToString();//获取值的sql Command
                    sql_command = sql_command.Replace("[" + col.ColumnName + "]", row.ItemArray[sourceDT.Columns[col.ColumnName].Ordinal].ToString());
                    string convert_function = _row.ItemArray[dt.Columns["convert_function"].Ordinal] == null ? "" : _row.ItemArray[dt.Columns["convert_function"].Ordinal].ToString();//获取值的function
                    convert_function = convert_function.Replace("[" + col.ColumnName + "]", row.ItemArray[sourceDT.Columns[col.ColumnName].Ordinal].ToString());
                    DataTable resultDT = null;
                    if (string.IsNullOrEmpty(sql_command))
                    {
                        resultDT = bll.GetDataBySqlComm(sql_command);
                    }
                    else
                    {
                        if (string.IsNullOrEmpty(convert_function))
                        {
                            resultDT = bll.GetDataBySqlComm(convert_function);
                        }
                    }

                    if (resultDT != null && resultDT.Rows.Count > 0)
                    {
                        entity.SetValue(col.ColumnName, resultDT.Rows[0].ItemArray[0]);//结果只有一个值;
                    }
                }
                bll.entity = entity;
                bll.Insert();
            }
        }

        public void Import(DataTable sourceDT, string TableName, string user_id = "")
        {
            BaseBussnies bll = new BaseBussnies();
            System.Data.DataTable main_dt = bll.GetDataBySqlComm("select * from t_sys_import_main where table_name='" + TableName + "'");
            string isSingleImport = "";
            string sqlCommand = "";
            string getKeySqlCommand = "";
            if (main_dt != null && main_dt.Rows.Count > 0)
            {
                isSingleImport = main_dt.Rows[0].ItemArray[main_dt.Columns["is_single_import"].Ordinal] as string;
                sqlCommand = main_dt.Rows[0].ItemArray[main_dt.Columns["sql_command"].Ordinal] as string;
                getKeySqlCommand = main_dt.Rows[0].ItemArray[main_dt.Columns["get_key_command"].Ordinal] as string;
            }



            System.Data.DataTable dt = bll.GetDataBySqlComm("select * from t_sys_import_setting where table_name='" + TableName + "'");
            dt.Select().ToList();

            
            if (getKeySqlCommand == "")
            {
                getKeySqlCommand = "select id from " + TableName + " where ";
                string fields = "";
                DataRow[] rows = dt.Select("is_key_field='Y'");
                if(rows !=null || rows.Count()>0)
                {
                    foreach(DataRow row in rows)
                    {
                        string colName = row.ItemArray[sourceDT.Columns["field_name"].Ordinal].ToString();
                        fields = fields == "" ? colName + "='{" + TableName + "}'" : fields + " and " + colName + "='{" + colName + "}'";
                    }
                }
                if(fields!="")
                {
                    getKeySqlCommand = getKeySqlCommand + fields;
                }
                else
                {
                    getKeySqlCommand = "";
                }
            }
            
            
            (new FL.DataImport.DataConvert()).UpdateDataTableColumnName(sourceDT, dt);

            foreach (DataRow row in sourceDT.Rows)
            {                /// 如果没命令
                if (string.IsNullOrEmpty(sqlCommand))
                {
                    DataTableBaseEntity entity = new DataTableBaseEntity();
                    entity.TableName = TableName;
                    string[] strKeyArrary = entity.GetKey();
                    if (strKeyArrary != null)
                    {
                        entity.KeyField = strKeyArrary[0];
                        entity.KeyFieldDataType = strKeyArrary[1];
                    }
                    entity.CreateDT();
                    try
                    {
                        //entity.FieldValue["add_time"] = System.DateTime.Now;
                        entity.SetValue("add_time", System.DateTime.Now);
                        if (user_id != "")
                        {
                            entity.SetValue("add_user", user_id);
                        }
                    }
                    catch (Exception ex) { }


                    string _getKeySqlCommand = getKeySqlCommand;
                    foreach (System.Data.DataColumn col in sourceDT.Columns)
                    {
                        try
                        {
                            entity.SetValue(col.ColumnName.Trim(), row.ItemArray[sourceDT.Columns[col.ColumnName].Ordinal]);
                            if (col.ColumnName == "employee_code")
                            {
                                DataRow dr = GetEmployee(row.ItemArray[sourceDT.Columns["employee_code"].Ordinal].ToString());
                                entity.SetValue("employee_id", dr.ItemArray[dr.Table.Columns["id"].Ordinal]);
                            }
                        }
                        catch (Exception ex) { }
                        try
                        {

                            DataRow _row = dt.Select("field_name='" + col.ColumnName.Trim() + "'").First();
                            string sql_command = _row.ItemArray[dt.Columns["sql_command"].Ordinal] == null ? "" : _row.ItemArray[dt.Columns["sql_command"].Ordinal].ToString();//获取值的sql Command
                            sql_command = sql_command.Replace("[" + col.ColumnName + "]", row.ItemArray[sourceDT.Columns[col.ColumnName].Ordinal].ToString());
                            string convert_function = _row.ItemArray[dt.Columns["convert_function"].Ordinal] == null ? "" : _row.ItemArray[dt.Columns["convert_function"].Ordinal].ToString();//获取值的function
                            convert_function = convert_function.Replace("[" + col.ColumnName + "]", row.ItemArray[sourceDT.Columns[col.ColumnName].Ordinal].ToString());
                            DataTable resultDT = null;
                            if (!string.IsNullOrEmpty(sql_command))
                            {
                                resultDT = bll.GetDataBySqlComm(sql_command);
                            }
                            else
                            {
                                if (!string.IsNullOrEmpty(convert_function))
                                {
                                    resultDT = bll.GetDataBySqlComm(convert_function);
                                }
                            }

                            if (resultDT != null && resultDT.Rows.Count > 0)
                            {
                                entity.SetValue(col.ColumnName, resultDT.Rows[0].ItemArray[0]);//结果只有一个值;
                            }
                            if (!string.IsNullOrEmpty(_getKeySqlCommand))
                            {
                                _getKeySqlCommand = _getKeySqlCommand.Replace("{" + col.ColumnName + "}", entity[col.ColumnName].ToString());
                            }


                        }
                        catch (Exception ex)
                        {
                            FL.Log.XTrace.WriteLine("-----------------------------------------------{0}", "");
                            //FL.Log.XTrace.WriteLine("表：{0}", para[0]);
                            //FL.Log.XTrace.WriteLine("记录：{0}", para[1]);
                            FL.Log.XTrace.WriteLine("错误：{1}", ex.Message);
                            //写调入日志
                            //写调入日志
                        }



                        bll.entity = entity;
                        DataTable _dt = bll.GetDataBySqlComm(_getKeySqlCommand);
                        if (_dt != null && _dt.Rows.Count > 0)
                        {
                            entity["id"] = _dt.Rows[0].ItemArray[0];
                            bll.Update();
                        }
                        else
                        {
                            bll.Insert();
                        }
                    }
                }
                else
                {
                    //string[] dataArray = strJSON.Split(s1, StringSplitOptions.None); --//
                    //执行导入数据
                    string[] s1 = { "[@@@]" };
                    //object[] para = (TableName + "[@@@]" + FL.Common.Globals.DataRowToJSON(entity.DRow)).Split(s1, StringSplitOptions.None);
                    object[] para = (TableName + "[@@@]" + FL.Common.Globals.DataRowToJSON(row)).Split(s1, StringSplitOptions.None);
                    try
                    {
                        bll.RunProcedure(sqlCommand, para);
                    }
                    catch (Exception ex)
                    {
                        FL.Log.XTrace.WriteLine("-----------------------------------------------{0}", "");
                        FL.Log.XTrace.WriteLine("表：{0}", para[0]);
                        FL.Log.XTrace.WriteLine("记录：{0}", para[1]);
                        FL.Log.XTrace.WriteLine("错误：{1}", ex.Message);
                        //写调入日志
                    }
                }

            }
        }

        /// <summary>
        /// 转换 datatable 列名
        /// </summary>
        /// <param name="sourceDT">数据源的datatable</param>
        /// <param name="TableName"></param>
        public void ConvertDataTableColumnName(DataTable sourceDT, string TableName)
        {
            BaseBussnies bll = new BaseBussnies();
            System.Data.DataTable dt = bll.GetDataBySqlComm("select * from t_sys_import_setting where table_name='" + TableName + "'");
            dt.Select().ToList();
            (new FL.DataImport.DataConvert()).UpdateDataTableColumnName(sourceDT, dt);
        }

        /// <summary>
        /// 获取人事信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetEmployees()
        {
            BaseBussnies bll = new BaseBussnies();
            return bll.GetDataBySqlComm("select * from v_hr_employee");
        }

        /// <summary>
        /// 根据员工编码获取人事信息
        /// </summary>
        /// <param name="EmployeeCode">员工编码</param>
        /// <returns></returns>
        public DataRow GetEmployee(string EmployeeCode)
        {
            if (EmployeesData == null)
            {
                EmployeesData = GetEmployees();
            }
            DataRow[] drs = EmployeesData.Select("employee_code='" + EmployeeCode + "'");
            if (drs.Count() > 0)
            {
                return drs[0];
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        private DataTable _EmployeesData = null;
        public DataTable EmployeesData
        {
            get
            {
                return _EmployeesData;
            }
            set
            {
                _EmployeesData = value;
            }
        }

        /// <summary>
        /// 数据导入
        /// </summary>
        /// <param name="FileName">文件</param>
        /// <param name="TableName">表名</param>
        public void Import(string FileName, string TableName)
        {
            //iSun.Bussiness.Base.BaseBussnies bll = new BaseBussnies();
            //System.Data.DataTable dt = bll.GetDataBySqlComm("Select * from SysImportSetting where tableName='"+TableName+"'");
            DataTable dt2 = GetExeclTable(FileName);
            Import(dt2, TableName);
        }
    }
}
